package com.to8to.autotask;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.to8to.autotask.util.DBUtil;

public class ShowFields
{
    public static void main(String[] args) throws SQLException, ClassNotFoundException
    {
        List<String> tableNameList = new ArrayList<String>();

        Connection connect = DBUtil.getConnection();
        Statement stat = connect.createStatement();
        ResultSet result = stat.executeQuery("show tables");
        while (result.next())
            tableNameList.add(result.getString(1));

        tableNameList.remove("xx_info");
        tableNameList.remove("yy_info");
        tableNameList.remove("uc_tags");

        DBUtil.releaseSource(result, stat, null);

        Map<String, List<String>> oldMap = new HashMap<String, List<String>>();
        Map<String, List<String>> newMap = new HashMap<String, List<String>>();

        for (String tableName : tableNameList)
        {
            String sql =
                String.format("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = '%s'  and table_schema = 'crmtest'", tableName);
            stat = connect.createStatement();
            result = stat.executeQuery(sql);

            oldMap.put(tableName, new ArrayList<String>());

            while (result.next())
                oldMap.get(tableName).add(result.getString(1));
        }

        DBUtil.releaseSource(result, stat, connect);

        Class.forName("com.mysql.jdbc.Driver");
        connect =
            DriverManager.getConnection("jdbc:mysql://192.168.1.57:3306/crm211?characterEncoding=utf-8&allowMultiQueries=true", "crm", "123456");

        for (String tableName : tableNameList)
        {
            String sql =
                String.format("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = '%s' and table_schema = 'crm211'", tableName);
            stat = connect.createStatement();
            result = stat.executeQuery(sql);

            newMap.put(tableName, new ArrayList<String>());

            while (result.next())
                newMap.get(tableName).add(result.getString(1));
        }

        compare(oldMap, newMap);

        DBUtil.releaseSource(result, stat, connect);
    }

    private static void compare(Map<String, List<String>> oldMap, Map<String, List<String>> newMap)
    {
        System.out.println("\r\n=============crmtest存在，而crm211不存在的字段=============\r\n");

        for (String tableName : oldMap.keySet())
        {
            List<String> oldFields = oldMap.get(tableName);
            List<String> newFileds = newMap.get(tableName);

            for (String field : oldFields)
            {
                if (!newFileds.contains(field))
                {
                    System.out.println(tableName + ": " + field);
                }
            }
        }

        System.out.println("\r\n=============crm211存在，而crmtest不存在的字段=============\r\n");

        for (String tableName : newMap.keySet())
        {
            List<String> oldFields = oldMap.get(tableName);
            List<String> newFileds = newMap.get(tableName);

            for (String field : newFileds)
            {
                if (!oldFields.contains(field))
                {
                    System.out.println(tableName + ": " + field);
                }
            }
        }
    }
}
